import re
import openpyxl

def check_ip(ipAddr):
    pattern1 = re.compile(r'^(2(5[0-5]{1}|[0-4]\d{1})|[0-1]?\d{1,2})(\.(2(5[0-5]{1}|[0-4]\d{1})|[0-1]?\d{1,2})){3}$')   
    if pattern1.match(ipAddr):
        return True
    else:
        return False

def check_file_ip(filename):
    wb = openpyxl.load_workbook(filename) 
    sheet = wb['Sheet']   
    for rownum in range(2,sheet.max_row+1):                #从1开始计数，第一行是列名，跳过
        ipStr  = sheet.cell(row=rownum, column=2).value   
        if type(ipStr) != str:
            print('IP Error Row:%d'%rownum)
            continue
        ipStr = ipStr.rstrip('\r\n').rstrip('\n').strip()
        if not check_ip(ipStr):
            print('IP Error Row:%d'%rownum)
            continue

def deal_file(filename):
    wb = openpyxl.load_workbook(filename)
    sheet = wb['Sheet'] 
    print('Total rows:%d'%(sheet.max_row))
    total_del_count = 0
    dup_ip_count = 0
    max_row = sheet.max_row

    for rownum in range(2,max_row+1):
        sheet.cell(row=rownum, column=2).value = sheet.cell(row=rownum, column=2).value.rstrip('\r\n').rstrip('\n').strip()
    rownum = 2
    while rownum <= max_row: 
        ipStr  = sheet.cell(row=rownum, column=2).value    #从1开始计数
        j = rownum + 1
        dup_count = 0
        while j <= max_row:
            if ipStr == sheet.cell(row=j, column=2).value :
                sheet.delete_rows(j)
                max_row -= 1
                dup_count += 1
            else:
                j += 1
        if dup_count > 0:
            dup_ip_count += 1
            print('%15s,%d '%(ipStr,dup_count),'\t',end=' ')
            total_del_count += dup_count
            if dup_ip_count % 6 == 0:
                print('')
        rownum += 1
    print('\r\nDelete rows:%d, dup ips:%d'%(total_del_count,dup_ip_count))
    
    wb.save("temp.xlsx")

if  __name__ == '__main__':
    filename = '主机.xlsx'
    #check_file_ip(filename)
    deal_file(filename)
    